package dao;

import domain.Order;
import domain.OrderItem;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 订单的dao，访问数据库完成订单创建（含将订单单项写入数据库）
 */
public class OrderDao {
    public boolean insert(Order order){
        /*
        添加订单的操作（含将订单单项写入数据库）
         */
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "INSERT INTO orders(oid,raddr,rname,rphone,remail,ordertime,user_id) VALUES('"+
                    order.getId()+"','"+
                    order.getRaddr()+"','"+
                    order.getRname()+"','"+
                    order.getRphone()+"','"+
                    order.getRemail()+"','"+
                    new Timestamp(order.getOrdertime().getTime())+"','"+
                    order.getUid()+"')";
            int num = stmt.executeUpdate(sql);
            if (num==0)return false;
            for (OrderItem item: order.getItems()){
                sql = "INSERT INTO items(order_id,product_id,count) VALUES('"+
                    item.getOid()+"','"+
                    item.getPid()+"','"+
                    item.getCount()+"')";
                num = stmt.executeUpdate(sql);
                sql = "UPDATE products SET count=count-"+item.getCount()+" WHERE pid='"+item.getPid()+"'";
                int num1 = stmt.executeUpdate(sql);
                if (num==0||num1==0)return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(stmt, conn);
        }
        return false;
    }

    public List<Object[]> download(String year, String month){
        /*
        月度销售榜单下载
         */
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Object[]> salesList = new ArrayList<>();
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT products.pname product_name,SUM(items.count) total FROM orders,products,items " +
                    "WHERE orders.oid=items.order_id AND products.pid=items.product_id " +
                    "AND year(ordertime)='"+year+"' AND month(ordertime)='"+month+"' " +
                    "GROUP BY products.pname ORDER BY total DESC";
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                Object[] item = new Object[2];
                item[0] = rs.getString("product_name");
                item[1] = rs.getInt("total");
                salesList.add(item);
            }
            return salesList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }

    public List<Order> browse(){
        /*
        查询所有订单
         */
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Order> orders = new ArrayList<>();
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM orders ORDER BY ordertime DESC";
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                Order order = new Order();
                order.setId(rs.getString("oid"));
                order.setRaddr(rs.getString("raddr"));
                order.setRname(rs.getString("rname"));
                order.setRphone(rs.getString("rphone"));
                order.setRemail(rs.getString("remail"));
                order.setOrdertime(new Date(rs.getTimestamp("ordertime").getTime()));
                order.setUid(rs.getInt("user_id"));
                orders.add(order);
            }
            return orders;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }

    public Order search(String id){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM orders WHERE oid='"+id+"'";
            rs = stmt.executeQuery(sql);
            if (rs.next()){
                Order order = new Order();
                List<OrderItem> items = new ArrayList<>();
                order.setId(rs.getString("oid"));
                order.setRaddr(rs.getString("raddr"));
                order.setRname(rs.getString("rname"));
                order.setRphone(rs.getString("rphone"));
                order.setRemail(rs.getString("remail"));
                order.setOrdertime(new Date(rs.getTimestamp("ordertime").getTime()));
                order.setUid(rs.getInt("user_id"));
                sql = "SELECT * FROM items WHERE order_id='"+id+"'";
                rs = stmt.executeQuery(sql);
                while (rs.next()){
                    OrderItem item = new OrderItem();
                    item.setOid(id);
                    item.setPid(rs.getInt("product_id"));
                    item.setCount(rs.getInt("count"));
                    items.add(item);
                }
                order.setItems(items);
                return order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }
}
